using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Globalization;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Server;

public partial class IzvodFunkcije
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 BrojDepozitne(SqlBinary _datoteka)
    {
        MemoryStream _stream = new MemoryStream(_datoteka.Value);
        _stream.Position = 0;
       Encoding encoding = Encoding.GetEncoding("Windows-1250");
      //   Encoding encoding = new UTF8Encoding();
        TextReader tr = new StreamReader(_stream,  encoding);
     //   tr.ReadLine();
        string line;
        
            if ((line = tr.ReadLine()) != null )
            {

                if (line.Substring(line.Length - 1, 1) != "3")
                {

                    line = tr.ReadLine();
                }
             
                if (line.Substring(line.Length - 3, 3) == "903")
                {
                    return GetSlogZiro903(line);
                }
                else
                {
                    return GetSlogZiro3(line);
                }


            }
            else
            {
                return -1;
            }
        // Put your code here
        
    }
        static readonly CultureInfo provider = CultureInfo.InvariantCulture;
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlDateTime DatumKreiranjaIzvoda(SqlBinary _datoteka)
    {
        // Put your code here
        MemoryStream _stream = new MemoryStream(_datoteka.Value);
        _stream.Position = 0;
        //  Encoding encoding = Encoding.GetEncoding("Windows-1250");
        Encoding encoding = new UTF8Encoding();
        TextReader tr = new StreamReader(_stream, encoding);
        //  tr.ReadLine();
        string line;
        bool noviformat = false;
        if ((line = tr.ReadLine()) != null)
        {
             
            if (line.Substring(line.Length - 1, 1) != "3")
            {
             
                line = tr.ReadLine();
            }
             if (line.Substring(line.Length-3,3)== "903")
                    {
                        noviformat = true;
                    }
            if (!noviformat)
                return GetSlogDateTime3(line);
            else
            {
                return GetSlogDateTime903(line);
            }
        }
        else
        {
            return new DateTime(2012,1,1);
        }
    }

    private static DateTime GetSlogDateTime3(string line)
    {
        bool result = false;
        int testint = 0;
        DateTime _datum;
        int brojizvatka;
        if (line.Substring(line.Length - 1, 1) == "3" 
            //&& int.TryParse(line.Substring(0, 10), out testint)
            //&& line.Substring(10, 8) == "        "
            ////&& line.Substring(63, 5) == "00000"
            ////&& line.Substring(71, 1) == " "
            ////&& line.Substring(106, 3) == "000"
            ////&& line.Substring(109, 2) == "  "
            ////&& line.Substring(111, 3) == "250"
            //&& line.Substring(121, 2) == "N "
            //&& int.TryParse(line.Substring(53, 3), out brojizvatka)
            //&& int.TryParse(line.Substring(114, 7), out testint)
            //&& DateTime.TryParse(line.Substring(191, 2) + "." + line.Substring(193, 2) + "." + line.Substring(195, 4), out _datum)
            //&& line.Substring(199, 50) == "                                                  "
            )
        {
            //
            int.TryParse(line.Substring(0, 10), out testint);
            int.TryParse(line.Substring(53, 3), out brojizvatka);
            int.TryParse(line.Substring(114, 7), out testint);
//            CultureInfo ci = CultureInfo.GetCultureInfo("en-US");
//string[] fmts = ci.DateTimeFormat.GetAllDateTimePatterns();
//Console.WriteLine(String.Join("\r\n", fmts));
            DateTime.TryParseExact(
                line.Substring(191, 2) + "." + line.Substring(193, 2) + "." + line.Substring(195, 4),  "dd.MM.yyyy", provider, DateTimeStyles.None, out _datum);
            
            
            //

            //if ((slog3 - slog7) == 0)
            //{
            //    slog3 += 1;
            //    //transakcijskiracunvlasnika = line.Substring(114, 7) + "-" + line.Substring(0, 10);
            //    //vlasnik = line.Substring(18, 35);
            //    //vlasnik = vlasnik.TrimEnd(' ');
                return _datum;


                
            //}
        }
        return new DateTime(2012,3,1);;
    }
    private static DateTime GetSlogDateTime903(string line)
    {
        bool result = false;
        int testint = 0;
        DateTime _datum;
        int brojizvatka;
        if (line.Substring(line.Length - 3, 3) == "903" 
            //&& int.TryParse(line.Substring(0, 10), out testint)
            //&& line.Substring(10, 8) == "        "
            ////&& line.Substring(63, 5) == "00000"
            ////&& line.Substring(71, 1) == " "
            ////&& line.Substring(106, 3) == "000"
            ////&& line.Substring(109, 2) == "  "
            ////&& line.Substring(111, 3) == "250"
            //&& line.Substring(121, 2) == "N "
            //&& int.TryParse(line.Substring(53, 3), out brojizvatka)
            //&& int.TryParse(line.Substring(114, 7), out testint)
            //&& DateTime.TryParse(line.Substring(191, 2) + "." + line.Substring(193, 2) + "." + line.Substring(195, 4), out _datum)
            //&& line.Substring(199, 50) == "                                                  "
            )
        {
            //
            //int.TryParse(line.Substring(0, 10), out testint);
            //int.TryParse(line.Substring(53, 3), out brojizvatka);
            //int.TryParse(line.Substring(114, 7), out testint);
           
//            CultureInfo ci = CultureInfo.GetCultureInfo("en-US");
//string[] fmts = ci.DateTimeFormat.GetAllDateTimePatterns();
            DateTime.TryParseExact(
                line.Substring(173 + 6 - 1, 2) + "." + line.Substring(173 - 1 + 4, 2) + "." + line.Substring(173 - 1, 4),
  "dd.MM.yyyy", provider, DateTimeStyles.None, out _datum);
         
            //

            //if ((slog3 - slog7) == 0)
            //{
            //    slog3 += 1;
            //    //transakcijskiracunvlasnika = line.Substring(114, 7) + "-" + line.Substring(0, 10);
            //    //vlasnik = line.Substring(18, 35);
            //    //vlasnik = vlasnik.TrimEnd(' ');
                return _datum;


                
            //}
        }
        return new DateTime(2012,2,1);
    }
    private static int GetSlogZiro3(string line)
    {
        bool result = false;
        int testint = 0;
        DateTime _datum;
        int brojizvatka;
        if (line.Substring(line.Length - 1, 1) == "3" 
            //&& int.TryParse(line.Substring(0, 10), out testint)
            //&& line.Substring(10, 8) == "        "
            ////&& line.Substring(63, 5) == "00000"
            ////&& line.Substring(71, 1) == " "
            ////&& line.Substring(106, 3) == "000"
            ////&& line.Substring(109, 2) == "  "
            ////&& line.Substring(111, 3) == "250"
            //&& line.Substring(121, 2) == "N "
            //&& int.TryParse(line.Substring(53, 3), out brojizvatka)
            //&& int.TryParse(line.Substring(114, 7), out testint)
            //&& DateTime.TryParse(line.Substring(191, 2) + "." + line.Substring(193, 2) + "." + line.Substring(195, 4), out _datum)
            //&& line.Substring(199, 50) == "                                                  "
            )
        {
            //
            int.TryParse(line.Substring(0, 10), out testint);
            int.TryParse(line.Substring(53, 3), out brojizvatka);
            int.TryParse(line.Substring(114, 7), out testint);
//                    CultureInfo ci = CultureInfo.GetCultureInfo("en-US");
//string[] fmts = ci.DateTimeFormat.GetAllDateTimePatterns();
//Console.WriteLine(String.Join("\r\n", fmts));
            DateTime.TryParseExact(
                line.Substring(191, 2) + "." + line.Substring(193, 2) + "." + line.Substring(195, 4),   "dd.MM.yyyy", provider, DateTimeStyles.None, out _datum);
            //DateTime.TryParse(,
            //                  out _datum);
            //

            //if ((slog3 - slog7) == 0)
            //{
            //    slog3 += 1;
            //    //transakcijskiracunvlasnika = line.Substring(114, 7) + "-" + line.Substring(0, 10);
            //    //vlasnik = line.Substring(18, 35);
            //    //vlasnik = vlasnik.TrimEnd(' ')
            int br = 0;
            int.TryParse(line.Substring(114, 7), out br);
            return br;



            //}
        }
        return -3;
    }
      private static int GetSlogZiro903(string line)
    {
        bool result = false;
        int testint = 0;
        DateTime _datum;
        int brojizvatka;
        if (line.Substring(line.Length - 3, 3) == "903" 
            //&& int.TryParse(line.Substring(0, 10), out testint)
            //&& line.Substring(10, 8) == "        "
            ////&& line.Substring(63, 5) == "00000"
            ////&& line.Substring(71, 1) == " "
            ////&& line.Substring(106, 3) == "000"
            ////&& line.Substring(109, 2) == "  "
            ////&& line.Substring(111, 3) == "250"
            //&& line.Substring(121, 2) == "N "
            //&& int.TryParse(line.Substring(53, 3), out brojizvatka)
            //&& int.TryParse(line.Substring(114, 7), out testint)
            //&& DateTime.TryParse(line.Substring(191, 2) + "." + line.Substring(193, 2) + "." + line.Substring(195, 4), out _datum)
            //&& line.Substring(199, 50) == "                                                  "
            )
        {
            //
          //  int.TryParse(line.Substring(0, 10), out testint);
           // int.TryParse(line.Substring(53, 3), out brojizvatka);
           // int.TryParse(line.Substring(114, 7), out testint);
            //DateTime.TryParse(line.Substring(173+4-1, 2) + "." + line.Substring(173-1+6, 2) + "." + line.Substring(173-1, 4),
            //                  out _datum);
            //

            //if ((slog3 - slog7) == 0)
            //{
            //    slog3 += 1;
            //    //transakcijskiracunvlasnika = line.Substring(114, 7) + "-" + line.Substring(0, 10);
            //    //vlasnik = line.Substring(18, 35);
            //    //vlasnik = vlasnik.TrimEnd(' ')
            int br = 0;
            int.TryParse(line.Substring(0, 7), out br);
            return br;



            //}
        }
        return -2;
    }

};

